﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using System.Text;
using InteropExcel = Microsoft.Office.Interop.Excel;

namespace FA_Commons.Excel
{
    public class OutPutEcxel
    {
        public void OutPut(string fn, HttpResponse resp, DataTable dt)
        {
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + fn + ".xls");
            string colHeaders = "", ls_item = "";
            int i = 0;

            //定义表对象与行对像，同时用DataSet对其值进行初始化 
            DataRow[] myRow = dt.Select("");

            //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
            for (i = 0; i < dt.Columns.Count - 1; i++)
                colHeaders += dt.Columns[i].Caption.ToString() + "\t";
            colHeaders += dt.Columns[i].Caption.ToString() + "\n";
            //向HTTP输出流中写入取得的数据信息 
            resp.Write(colHeaders);
            //逐行处理数据 
            foreach (DataRow row in myRow)
            {
                //在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n 
                for (i = 0; i < row.ItemArray.Length - 1; i++)
                    ls_item += row[i].ToString() + "\t";
                ls_item += row[i].ToString() + "\n";
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 
                resp.Write(ls_item);
                ls_item = "";
            }


            //写缓冲区中的数据到HTTP头文件中 
            resp.End();
        }

        private static void DisableControls(GridView gv)
        {
           //删除第一列“选择”列
            
            gv.HeaderRow.Cells.RemoveAt(0);
            string name = String.Empty;
            for (int i = 0; i < gv.Rows.Count; i++)
            {
                gv.Rows[i].Cells.RemoveAt(0);
                for (int j = 0; j < gv.Rows[i].Cells.Count; j++)
                {
                    for (int m = 0; m < gv.Rows[i].Cells[j].Controls.Count; m++)
                    {
                        name = "";
                        if (gv.Rows[i].Cells[j].Controls[m].GetType() == typeof(LinkButton))
                        {
                            name = (gv.Rows[i].Cells[j].Controls[m] as LinkButton).Text;
                            gv.Rows[i].Cells[j].Controls.Remove(gv.Rows[i].Cells[j].Controls[m]);
                            gv.Rows[i].Cells[j].Text = name;
                        }

                        else if (gv.Rows[i].Cells[j].Controls[m].GetType() == typeof(DropDownList))
                        {
                            name = (gv.Rows[i].Cells[j].Controls[m] as DropDownList).SelectedItem.Text;
                            gv.Rows[i].Cells[j].Controls.Remove(gv.Rows[i].Cells[j].Controls[m]);
                            gv.Rows[i].Cells[j].Text = name;
                        }
                        else if (gv.Rows[i].Cells[j].Controls[m].GetType() == typeof(CheckBox))
                        {
                            gv.Rows[i].Cells[j].Controls.Remove(gv.Rows[i].Cells[j].Controls[m]);
                        }
                    }
                }
            }


            //for (int j = 0; j < gv.HeaderRow.Cells.Count; j++)
            //{
            //    for (int m = 0; m < gv.HeaderRow.Cells[j].Controls.Count; m++)
            //    {
            //        name = "";
            //        if (gv.HeaderRow.Cells[j].Controls[m].GetType() == typeof(LinkButton) || gv.HeaderRow.Cells[j].Controls[m].GetType().Name=="DataControlLinkButton")
            //        {
            //            name = (gv.HeaderRow.Cells[j].Controls[m] as LinkButton).Text;
            //            gv.HeaderRow.Cells[j].Controls.Remove(gv.HeaderRow.Cells[j].Controls[m]);
            //            gv.HeaderRow.Cells[j].Text = name;
            //        }

            //        else if (gv.HeaderRow.Cells[j].Controls[m].GetType() == typeof(DropDownList))
            //        {
            //            name = (gv.HeaderRow.Cells[j].Controls[m] as DropDownList).SelectedItem.Text;
            //            gv.HeaderRow.Cells[j].Controls.Remove(gv.HeaderRow.Cells[j].Controls[m]);
            //            gv.HeaderRow.Cells[j].Text = name;
            //        }
            //        else if (gv.HeaderRow.Cells[j].Controls[m].GetType() == typeof(CheckBox))
            //        {
            //            gv.HeaderRow.Cells[j].Controls.Remove(gv.HeaderRow.Cells[j].Controls[m]);
            //        }
            //    }
            //}
            //本程序特殊处理上边为通用处理
            for (int j = 0; j < gv.HeaderRow.Cells.Count; j++)
            {
                //处理国家名称列
                if (j == 1 && gv.HeaderRow.Cells[j].Controls.Count>0)
                {
                    name = (gv.HeaderRow.Cells[j].Controls[0] as LinkButton).Text;
                    gv.HeaderRow.Cells[j].Controls.Clear();
                    gv.HeaderRow.Cells[j].Text = name;
                    continue;
                }
                if (gv.HeaderRow.Cells[j].Controls.Count > 0 && gv.HeaderRow.Cells[j].Controls[0].Controls.Count > 0)
                {

                    for (int m = 0; m < gv.HeaderRow.Cells[j].Controls[0].Controls.Count; m++)
                    {
                        name = "";
                        if (gv.HeaderRow.Cells[j].Controls[0].Controls[m].Controls[0].Controls[0].GetType() == typeof(LinkButton) || gv.HeaderRow.Cells[j].Controls[0].Controls[m].Controls[0].Controls[0].GetType().Name == "DataControlLinkButton")
                        {
                            name = (gv.HeaderRow.Cells[j].Controls[0].Controls[m].Controls[0].Controls[0] as LinkButton).Text;
                            gv.HeaderRow.Cells[j].Controls.Clear();
                            gv.HeaderRow.Cells[j].Text = name;
                            break;
                        }
                        else
                        {
                            gv.HeaderRow.Cells[j].Controls.Clear();
                            break;
                        }
                    }
                }
            }
        }
        /// <summary>
        /// 将网格数据导出到Excel
        /// </summary>
        /// <param name="ctrl">网格名称(如GridView1)</param>
        /// <param name="FileType">要导出的文件类型(Excel:application/ms-excel)</param>
        /// <param name="FileName">要保存的文件名</param>
        public static void GridViewToExcel(GridView gv, string FileType, string FileName)
        {
            DisableControls(gv);
            HttpContext.Current.Response.Charset = "GB2312";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            HttpContext.Current.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + FileName);//HttpUtility.UrlEncode(FileName, System.Text.Encoding.GetEncoding("GB2312")).ToString());
            HttpContext.Current.Response.ContentType = FileType;//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword 
            gv.Page.EnableViewState = false;
            StringWriter tw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(tw);
            gv.RenderControl(hw);
            HttpContext.Current.Response.Write(tw.ToString());
            HttpContext.Current.Response.End();
        }


        public static void DatasetToExcel(DataSet ds, string filename, Page thisPage)
        {
            object oMiss = System.Reflection.Missing.Value;
            InteropExcel.Application xlApp = new InteropExcel.Application();
            try
            {
                InteropExcel.Workbook xlWorkbook = xlApp.Workbooks.Add(oMiss);
                InteropExcel.Worksheet xlWorksheet;
                //循环DataSet中的Table
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    xlWorksheet = (InteropExcel.Worksheet)xlWorkbook.Worksheets.Add(oMiss, oMiss, 1, oMiss);
                    //以table名作为新加的sheet页名
                    xlWorksheet.Name = "信息" + i.ToString();
                    //取出datatable中的所有值，缓存到stringBuffer中

                    for (int m = 0; m < ds.Tables[i].Columns.Count; m++)
                    {
                        xlWorksheet.Cells[1, m + 1] = ds.Tables[i].Columns[m].ColumnName;
                    }
                    for (int m = 0; m < ds.Tables[i].Rows.Count; m++)
                    {
                        for (int n = 0; n < ds.Tables[i].Columns.Count; n++)
                        {
                            try
                            {
                                xlWorksheet.Cells[m + 2, n + 1] = "'" + ds.Tables[i].Rows[m][n].ToString();
                            }
                            catch (System.Exception ex)
                            {
                                throw new System.Exception("向单元格(" + m.ToString() + ")写数据时出错：" + ex.Message, ex);
                            }
                        }
                    }
                }
                xlWorkbook.Saved = true;
                xlWorkbook.SaveCopyAs(HttpRuntime.AppDomainAppPath.ToString()+"report\\temp.xls");//先报生成的excel文件保存到服务器中
                xlWorkbook.Close(InteropExcel.XlSaveAction.xlSaveChanges, oMiss, oMiss);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
                xlWorkbook = null;
                //把Dataset中的数据存到服务器中结束
                //把文件发送到客户端
                string strFileName = HttpRuntime.AppDomainAppPath.ToString()+"report\\temp.xls";
                thisPage.Response.Clear();
                thisPage.Response.Charset = "utf-8";
                thisPage.Response.Buffer = true;
                thisPage.EnableViewState = false;
                thisPage.Response.ContentEncoding = System.Text.Encoding.UTF8;
                thisPage.Response.AppendHeader("Content-Disposition", "attachment;FileName=" + filename);
                thisPage.Response.WriteFile(strFileName);
                thisPage.Response.Flush();
                thisPage.Response.Close();
            }
            catch (System.Exception ex)
            {
                throw new System.Exception(ex.Message, ex);
            }
            finally
            {
                //释放
                xlApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                xlApp = null;
                GC.Collect();
                thisPage.Response.End();
            }
        }
    }
}
